<?php

class Application_Model_ShowLeagueTable
{
    protected $_dbAdapter;

    public function __construct() {
        $this->_dbAdapter = Application_Model_DbTable_Spiele::getDefaultAdapter();
    }
    public function getTable($ligaId)
    {
        $select      = $this->_dbAdapter->select();        
        $finalSelect = $this->_dbAdapter->select();
        
        $selectHeim = $this->_homeQuery($ligaId);
        $selectAusw = $this->_awayQuery($ligaId);
        
        
        $finalSelect->from(array('finalTable' => $select->union(array($selectHeim, $selectAusw))),
                           array('teamId'    => 'teams.teamId',
                                 'sp'        => 'SUM(sp)',
                                 's'         => 'SUM(s)',
                                 'u'         => 'SUM(u)',
                                 'v'         => 'SUM(v)',
                                 'pkt'       => 'SUM(pkt)',
                                 'toreGes'   => 'SUM(toreGes)',
                                 'toreBekom' => 'SUM(toreBekom)',
                                 'diff'      => 'SUM(toreGes) - SUM(toreBekom)',))
                    ->joinInner('teams', 'teams.teamId = finalTable.teamId')
                    ->group('teamId')
                    ->order(array('pkt DESC', 'diff DESC', 'toreGes DESC'));
        
        $stmt = $finalSelect->query();
        
        return $stmt->fetchAll();
    }
    
    public function getHomeTable($ligaId)
    {
        
    }

    public function getAwayTable($ligaId)
    {
        
    }


    protected function _homeQuery($ligaId)
    {
        $selectHeim = $this->_dbAdapter->select();
        
        // Heim Abfrage
        $selectHeim->from(array('spiele'),
                          array(
                              'teamId'    => 'heimTeamId',
                              'sp'        => 'SUM(IF(toreHeim IS NOT NULL, 1, 0))',
                              's'         => 'SUM(IF(toreHeim > toreAusw, 1, 0))',
                              'u'         => 'SUM(IF(toreHeim = toreAusw, 1, 0))',
                              'v'         => 'SUM(IF(toreHeim < toreAusw, 1, 0))',
                              'pkt'       => 'SUM(IF(toreHeim > toreAusw, 3, 0) + IF(toreHeim = toreAusw, 1, 0))',
                              'toreGes'   => 'SUM(toreHeim)',
                              'toreBekom' => 'SUM(toreAusw)',))
                   ->group('heimTeamId')
                   ->where('ligaId = ?', $ligaId);
        return $selectHeim;
    }
    
    protected function _awayQuery($ligaId)
    {
        $selectAusw  = $this->_dbAdapter->select();
        
        // Ausw Abfrage
        $selectAusw->from(array('spiele'),
                          array(
                              'teamId'    => 'auswTeamId',
                              'sp'        => 'SUM(IF(toreAusw IS NOT NULL, 1, 0))',
                              's'         => 'SUM(IF(toreHeim < toreAusw, 1, 0))',
                              'u'         => 'SUM(IF(toreHeim = toreAusw, 1, 0))',
                              'v'         => 'SUM(IF(toreHeim > toreAusw, 1, 0))',
                              'pkt'       => 'SUM(IF(toreAusw > toreHeim, 3, 0) + IF(toreHeim = toreAusw, 1, 0))',
                              'toreGes'   => 'SUM(toreAusw)',
                              'toreBekom' => 'SUM(toreHeim)',))
                   ->group('auswTeamId')
                   ->where('ligaId = ?', $ligaId);
        return $selectAusw;
    }
    
}

